﻿CREATE PROCEDURE [dbo].[spMomPortCustomers] ( @OwnerID int )
as
begin
	set nocount on

	declare @custnumCode char(1)
	set @custnumCode = 'C'
	declare @altnumCode char(1)
	set @altnumCode = 'D'

	declare @message nvarchar(1000)
	set @message = 'MOMPortCustomers' + '  ' + convert( varchar, getdate(), 9 )
	RAISERROR (@message, 0, 1) WITH NOWAIT

	select * into ##cust from openquery( MOMPort, 'select * from cust' )
	create index idx_tmpCust on ##cust (custnum)
	select * into ##country from openquery( MOMPort, 'select * from country' )
	create index idx_tmpCountry on ##country (name)
	create index idx_tmpCountry2 on ##country (country)

	declare @custnum int
	declare @AddressID int
	declare @PersonID int
	declare @CustomerID int
	declare cCust cursor local for select custnum from ##cust
	open cCust
	fetch next from cCust into @custNum
	while @@fetch_status = 0
	begin
		insert into tblAddress (Address1, Address2, Address3, City, State, PostalCode, CountryID )
		select addr, addr2, null, city, state, zipcode, coalesce( T3.CountryID, 1 )
		from ##cust T1
			left outer join ##country T2 on T1.country = T2.country
			left outer join codeCountry T3 on T2.name = T3.name
		where T1.custnum = @custnum
		set @AddressID = SCOPE_IDENTITY()

		insert into tblPerson ( FirstName, MiddleName, LastName, Name, LanguageID, AddressID )
		select FirstName, null, LastName, dbo.MakeName(FirstName, LastName), 1, @AddressID
		from ##cust where custnum = @custNum
		set @PersonID = SCOPE_IDENTITY()

		insert into tblContactPoint (ContactPointTypeCode, [value], PersonID )
		select 'P', phone, @PersonID
		from ##cust where custnum = @custNum and coalesce(phone, '') <> ''

		insert into tblCustomer ( AllowRent, AllowPhone, AllowMail, AllowEmail, AllowCleaning, ImportDate, DoNotProcess, Password, OwnerID, PersonID )
		select 1, 1, 1 - nomail, 1 - nomail, 1, 
			dbo.SmallDTCast(entrydate), 
			0, password, @OwnerID, @PersonID 
		from ##cust where custnum = @custnum
		set @CustomerID = SCOPE_IDENTITY()

		insert into tblCustomerSourceID (CustomerID, IDSourceCode, OwnerID, IDValue)
		values (@CustomerID, @custnumCode, @OwnerID, @custnum)
		insert into tblCustomerSourceID (CustomerID, IDSourceCode, OwnerID, IDValue)
		select @CustomerID, @altnumCode, @OwnerID, T1.altnum
		from ##cust T1
			left outer join tblCustomerSourceID T2 on T2.OwnerID = @OwnerID
				and T2.IDSourceCode = @altnumCode
				and cast(T1.altnum as varchar(20)) = T2.IDValue
		where T1.custnum = @custnum
			and T2.CustomerSourceID is null

		fetch next from cCust into @custNum
	end
	close cCust
	deallocate cCust
end
